﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using SEO.Model;
using System.Data.SQLite;

namespace SEO.DAL
{
    /// <summary>
    /// 版权所有: 版权所有(C) 2011，Adin Lee
    /// 内容摘要: 行业分类表
    /// 完成日期：2011年6月6日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class IndustryDAL
    {

        /// <summary>
        /// 获取Industry表中数据
        /// <returns>返回List</returns>
        /// </summary>
        public List<Industry> GetAllIndustryDAL()
        {
            List<Industry> list = new List<Industry>();
            string strSql = "select * from Industry";
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    Industry iy = new Industry();
                    iy.IndustryID = int.Parse(dr["industry_id"].ToString());
                    iy.IndustryName = dr["industry_name"].ToString();
                    iy.ParentID = int.Parse(dr["parent_id"].ToString());
                    list.Add(iy);
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定Industry -> " + ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 根据指定SQL语句获取Industry表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回List</returns>
        public List<Industry> GetIndustryByWhereDAL(string strSql)
        {
            List<Industry> list = new List<Industry>();
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    Industry iy = new Industry();
                    iy.IndustryID = int.Parse(dr["industry_id"].ToString());
                    iy.IndustryName = dr["industry_name"].ToString();
                    iy.ParentID = int.Parse(dr["parent_id"].ToString());
                    list.Add(iy);
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("根据指定SQL语句获取Industry表中数据 -> " + ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 往Industry表中插入数据
        /// <param name="ks">实例化Industry类</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int InsertIndustryDAL(SEO.Model.Industry iy)
        {
            int iNum = 0;
            string strSql = "insert into Industry(industry_id,industry_name,parent_id) " +
             " values(" + iy.IndustryID + ",'" + iy.IndustryName + "'," + iy.ParentID + ")";
            try
            {
                iNum = SEO.DBSqlHelp.DBSQLiteHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("往Industry表中插入数据 -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 根据指定条件删除Industry表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int DeleteIndustryDAL(string strSql)
        {
            int iNum = 0;
            try
            {
                iNum = SEO.DBSqlHelp.DBSQLiteHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("根据指定条件删除Industry表中数据 -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 更新Industry表中数据
        /// </summary>
        /// <param name="ks">实例化Industry类</param>
        /// <returns>返回影响行数</returns>
        public int UpdateIndustryDAL(SEO.Model.Industry iy)
        {
            int iNum = 0;
            string strSql = "update Industry set industry_name=" + iy.IndustryName + " where industry_id=" + iy.IndustryID;
            try
            {
                iNum = SEO.DBSqlHelp.DBSQLiteHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新Industry表中数据 -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 绑定表Industry到DataGridView
        /// <param name="dgv">DataGridView控件名称</param>
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        public void BindIndustryDAL(System.Windows.Forms.DataGridView dgv, string strSql)
        {
            try
            {
                //清除最后自动生成的行
                dgv.AutoGenerateColumns = false;
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataAdapter da = new SQLiteDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds, "Industry");
                ds.Tables["Industry"].Columns.Add("ViewCatch", typeof(string));
                for (int i = 0; i < ds.Tables["Industry"].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables["Industry"].Rows[i];
                }
                dgv.DataSource = ds.Tables["Industry"].DefaultView;
                da.Dispose();
                cn.Close();
                //清除当前选择行行为
                dgv.CurrentCell = null;

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定Industry -> " + ex.Message);
            }
        }

        /// <summary>
        /// 获取表中最大ID，并自增1
        /// </summary>
        /// <returns>返回表中最大ID，并自增1</returns>
        public int GetAutoAddNum()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select industry_id from Industry order by industry_id desc limit 1";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["industry_id"].ToString()) + 1;
                }
                else
                {
                    iNum = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("获取表中最大ID，并自增1 -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 检测是否已存在数据库中，屏蔽已抓取的数据
        /// </summary>
        /// <param name="strIndustryName">行业名称</param>
        /// <returns>返回 True or False</returns>
        public bool CheckKeywordsRankIsExist(string strIndustryName)
        {
            bool bValue = false;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select industry_id from KeywordsRank where industry_name='" + strIndustryName + "'";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    bValue = true;
                }
                else
                {
                    bValue = false;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("检测是否已存在数据库中 -> " + ex.Message);
            }

            return bValue;
        }

        /// <summary>
        /// 获取行业父ID
        /// </summary>
        /// <param name="iIndustryID">行业ID</param>
        /// <returns>返回行业父ID</returns>
        public int GetParentID(int iIndustryID)
        {
            int iParentID = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select parent_id from Industry where industry_id=" + iIndustryID;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iParentID = int.Parse(dr["parent_id"].ToString());
                }
                else
                {
                    iParentID = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("获取行业父ID -> " + ex.Message);
            }

            return iParentID;
        }

        /// <summary>
        /// 根据ID获取行业名称
        /// </summary>
        /// <param name="iIndustryID">行业ID</param>
        /// <returns>返回行业名称</returns>
        public string GetIndustryNameByID(int iIndustryID)
        {
            string strReturnValue = "";

            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select industry_name from Industry where industry_id=" + iIndustryID;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    strReturnValue = dr["industry_name"].ToString();
                }
                else
                {
                    strReturnValue = "";
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("根据ID获取行业名称 -> " + ex.Message);
            }

            return strReturnValue;
        }
    }
}
